package com.servlet;

import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.print.attribute.standard.PDLOverrideSupported;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSON;
import com.dao.*;
import com.jspsmart.upload.Files;
import com.jspsmart.upload.SmartUpload;
import com.model.CurriInfo;
import com.model.PaperInfo;
import com.model.PaperTitleInfo;
import com.model.PublishExamPlan;
import com.model.QuestionInfo;
import com.util.ModelQuestionInfo;
import com.util.StringUtil;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Alignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * Servlet implementation class StuServlet
 */
@WebServlet("/QuestionInfoServlet")
public class QuestionInfoServlet extends HttpServlet {
	QuestionInfoDao qid=new QuestionInfoDao();
	CurriInfoDao cd=new CurriInfoDao();
	PaperInfoDao pid=new PaperInfoDao();
	PaperTitleInfoDao ptid=new PaperTitleInfoDao();
	PublishExamPlanDao pepd=new PublishExamPlanDao();

	/**
	 * @see HttpServlet#service(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("UTF-8");
		response.setCharacterEncoding("UTF-8");
		String op=request.getParameter("op");
		

		
		if("select".equals(op)) {
			select(request,response);
		}else if("update".equals(op)) {
			update(request,response);
		}else if("delete".equals(op)) {
			delete(request,response);
		}else if("add".equals(op)) {
			add(request,response);
		}else if("adds".equals(op)) {
			adds(request,response);
		}else if("导入模版".equals(op)){
			drmb(request,response);
		}else if("导出".equals(op)) {
			dc(request,response);
		}else if("selectBystate".equals(op)) {
			if(request.getParameter("pd")==null) {
				qid.updateAllstate();
			}
			
			int page=Integer.parseInt(request.getParameter("page"));
			int rows=Integer.parseInt(request.getParameter("rows"));
			String name=request.getParameter("name");
			if(name==null) {
				name="";
			}
			int km=0;
			if(request.getParameter("km")!=null) {
				km=Integer.parseInt(request.getParameter("km"));
			}
			int sjid=0;
			if(request.getParameter("sjid")!=null) {
				sjid=Integer.parseInt(request.getParameter("sjid"));
			}

			if(km!=0&&sjid!=0) {
				List<Integer> list=ptid.selectAlltuidBySjid(sjid);
				for(int i=0;i<list.size();i++) {
					qid.updatestateByid(list.get(i));
				}
			}
			
			
			int sjcount=qid.selectAllBystatecount(name,km,sjid);
			ModelQuestionInfo m=qid.selectAllBystate(page, rows, name, km, sjid,sjcount);
			response.getWriter().print(JSON.toJSONString(m));
		}else if("cxts".equals(op)) {
			int sjid=0;
			if(request.getParameter("sjid")!=null) {
				sjid=Integer.parseInt(request.getParameter("sjid"));
			}
			response.getWriter().print(qid.selectcountById(sjid));
		}
		else if("sjbyid".equals(op)) {
			int km=0;
			if(request.getParameter("km")!=null) {
				km=Integer.parseInt(request.getParameter("km"));
			}
			
			ArrayList<PaperInfo> list=pid.selectnameById(km);
			response.getWriter().print(JSON.toJSONString(list));
		}else if("updateBystate".equals(op)) {
			int id=Integer.parseInt(request.getParameter("id"));
			PaperTitleInfo pi=new PaperTitleInfo();
			pi.setPaperId(Integer.parseInt(request.getParameter("sjid")));
			pi.setTimuId(id);
			if(qid.updatestateByid(id)==1&&ptid.insert(pi)==1) {
				response.getWriter().print("success");
			}else {
				response.getWriter().print("试题添加失败");
			}
		}else if("chakan".equals(op)) {
			int sjid=Integer.parseInt(request.getSession().getAttribute("sjid")+"");
			response.getWriter().print(JSON.toJSONString(qid.selectAll(sjid)));
		}
		else if("hqid".equals(op)) {
			request.getSession().setAttribute("sjid", request.getParameter("sjid"));
			response.getWriter().print("succ");
		}else if("updatesjstate".equals(op)){
			PaperTitleInfo pi=new PaperTitleInfo();
			pi.setPaperId(Integer.parseInt(request.getParameter("sjid")));
			pi.setTimuId(Integer.parseInt(request.getParameter("tkid")));
			if(ptid.delete(pi)==1) {
				response.getWriter().print("移除成功");
			}else {
				response.getWriter().print("移除失败");
			}
		}else if("fbks".equals(op)) {
			String kmid=request.getParameter("fbkm");
			String sjid=request.getParameter("fbsj");
			String[] bj=request.getParameterValues("fbbj");
			
			String start=request.getParameter("fbstart");
			int ends=Integer.parseInt(request.getParameter("fbend"));
			String riqi=request.getParameter("riqi");

			Date d=new Date(); 
			SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String end="";
			start=riqi+" "+start+":00";
			Date dst;
			try {
				dst = sdf.parse(start);
				long en=dst.getTime()+(ends*60000);
				Date e=new Date(en);
				end=sdf.format(e)+"";
			} catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			
			String time=sdf.format(d).toString();
			PublishExamPlan pe=new PublishExamPlan();
			int count=0;
			for(int i=0;i<bj.length;i++) {
				pe.setPaperId(Integer.parseInt(sjid));
				pe.setClassId(Integer.parseInt(bj[i]));
				pe.setStartTime(start);
				pe.setEndTime(end);
				pe.setCreatTime(time+"");
				count+=pepd.insert(pe);
			}
			count+=pid.updatestateById(Integer.parseInt(sjid));
			if(bj.length<count) {
				response.getWriter().print("seccuss");
			}else if(count>0) {
				response.getWriter().print("erros");
			}else {
				response.getWriter().print("erro");
			}
		}else if("cxfbbj".equals(op)) {
			ClassDao cd=new ClassDao();
			int sjid=Integer.parseInt(request.getParameter("sjid"));
			ArrayList<com.model.Class> list=cd.selectFbBjByAll(sjid);
			response.getWriter().print(JSON.toJSONString(list));
		}
	}
	
	
	protected void select(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		int page=Integer.parseInt(request.getParameter("page"));
		int rows=Integer.parseInt(request.getParameter("rows"));
		String name=request.getParameter("name");
		int km=0;
		if(request.getParameter("km")!=null) {
			km=Integer.parseInt(request.getParameter("km"));
		}
		
		ModelQuestionInfo m=qid.selectAll(page, rows, name, km);
		response.getWriter().print(JSON.toJSONString(m));
	}

	protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		QuestionInfo qi=new QuestionInfo();
		qi.setA(request.getParameter("A"));
		qi.setB(request.getParameter("B"));
		qi.setC(request.getParameter("C"));
		qi.setD(request.getParameter("D"));
		qi.setCurId(cd.selectIdByName(request.getParameter("curName")));
		qi.setTimuId(Integer.parseInt(request.getParameter("timuId")));
		qi.setTimuName(request.getParameter("timuName"));
		qi.setFenZhi(request.getParameter("fenZhi"));
		qi.setJieXi(request.getParameter("jieXi"));
		qi.setYes(request.getParameter("yes"));
		if(qid.updateBuId(qi)==1) {
			response.getWriter().print("修改成功");
		}
		else {
			response.getWriter().print("修改失败");
		}
	}
	
	protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		int id=Integer.parseInt(request.getParameter("id"));
		if(qid.deleteById(id)==1) {
			response.getWriter().print("删除成功");
		}else {
			response.getWriter().print("删除失败");
		}
	}
	
	protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		QuestionInfo qi=new QuestionInfo();
		qi.setA(request.getParameter("A"));
		qi.setB(request.getParameter("B"));
		qi.setC(request.getParameter("C"));
		qi.setD(request.getParameter("D"));
		qi.setCurId(Integer.parseInt(request.getParameter("curname")));
		qi.setTimuName(request.getParameter("timuname"));
		qi.setFenZhi(request.getParameter("fenzhi"));
		qi.setJieXi(request.getParameter("jiexi"));
		qi.setYes(request.getParameter("yes"));
		if(qid.insertAll(qi)==1) {
			response.getWriter().print("添加成功");
		}
		else {
			response.getWriter().print("添加失败");
		}
	}
	
	protected void adds(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("GBK");
		
		//定义文件名
				String fileName=StringUtil.getFileName();
				SmartUpload su=new SmartUpload();
				su.initialize(getServletConfig(), request, response);
				try {
					su.upload();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
					int count=0;
					Files fs=su.getFiles();
					com.jspsmart.upload.File f=fs.getFile(0);
					if("".equals(f.getFileName())||f.getFileName()==null) {
						response.getWriter().print("请先选择文件。");
					}
					try {
						//把文件另存为
						f.saveAs("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/"+f.getFileName());
						
						//找到工作簿
						Workbook book=Workbook.getWorkbook(new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/"+f.getFileName()));
						//找到工作表
						Sheet st=book.getSheet(0);
						Cell[] cel = null;
						int id=0;
						int coun=st.getRows();
						//通过循环，找到每行，然后在读每行的列
						for(int i=2;i<st.getRows();i++) {
							//创建实体类
							QuestionInfo qi=new QuestionInfo();
							//i表示的是行的下标
							cel=st.getRow(i);
							id=cd.selectIdByName(cel[0].getContents());
							if(id==0) {
								//关闭
								book.close();
								response.getWriter().print("导入科目不存在，请先添加改科目！");
								break;
							}
							qi.setCurId(id);
							qi.setTimuName(cel[1].getContents());
							qi.setA(cel[2].getContents());
							qi.setB(cel[3].getContents());
							qi.setC(cel[4].getContents());
							qi.setD(cel[5].getContents());
							qi.setYes(cel[6].getContents());
							qi.setJieXi(cel[7].getContents());
							qi.setFenZhi(cel[8].getContents());
							
							//添加到数据库
							qid.insertAll(qi);
							count++;
						}
						//关闭
						book.close();
						if(count==(coun-2)){
							response.getWriter().print("导入成功");
						}else if(count>0){
							response.getWriter().print("导入过程出现错误,请检查文档后重试！");
						}else {
							response.getWriter().print("导入的文档有误,请检查文档后重试！");
						}
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
	
	protected void drmb(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		SmartUpload su=new SmartUpload();
		su.initialize(getServletConfig(), request, response);
		try {
			su.upload();
			su.downloadFile("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/题库模版.xls");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
	protected void dc(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		request.setCharacterEncoding("GBK");
		response.setCharacterEncoding("GBK");

		String classname=request.getParameter("classnames");
		SmartUpload su=new SmartUpload();
		su.initialize(getServletConfig(), request, response);
		try {
			su.upload();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//定义File表示文件
		java.io.File f = new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student");
		//判断f锁表示的目录是否存在
		if(!f.exists())
		{
					// 创建这个目录
					f.mkdir();
		}
		java.io.File f1 = new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls");
		//判断文件是否存在
		if(!f1.exists())
		{
				try {
					f1.createNewFile();
				} catch (IOException e) {
						// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		//创建工作簿
		WritableWorkbook book = Workbook.createWorkbook(new java.io.File("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls"));
		
		
		
		//把导入.xls里的数据写到导出.xls表里
		try {
			
			if(classname.equals("全部科目")) {
				try {
					//存在学生的班级
					ArrayList<CurriInfo> cla=qid.selectByClassName();
					//所有学生信息
					ArrayList<QuestionInfo> stu=qid.selectByQbXueSheng();
					//有数据的班级总数
					int count=qid.selectByQbBjCount();
				
					
					//设置单元格的字体
					WritableFont font1=new WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD);
					WritableFont font2=new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
					WritableFont font3=new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD);
					//设置单元格的格式
					WritableCellFormat format1=new WritableCellFormat(font1);
					format1.setAlignment(Alignment.CENTRE);
					//设置边框
					format1.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format2=new WritableCellFormat(font2);
					format2.setAlignment(Alignment.CENTRE);
					//设置边框
					format2.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format3=new WritableCellFormat(font3);
					//设置边框
					format3.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					int row=0;
					for(int i=0;i<count;i++) {
					
						//创建工作表 
						WritableSheet sheet=book.createSheet(cla.get(i).getCurName()+"",i); 

						//设置单元格的大小
						sheet.mergeCells(0, 0, 8, 0);
						sheet.setColumnView(0, 20);
						sheet.setColumnView(1, 30);
						sheet.setColumnView(2, 30);
						sheet.setColumnView(3, 30);
						sheet.setColumnView(4, 30);
						sheet.setColumnView(5, 30);
						sheet.setColumnView(6, 30);
						sheet.setColumnView(7, 30);
						sheet.setColumnView(8, 20);
					
						//添加
						sheet.addCell(new Label(0,0,cla.get(i).getCurName(),format1));
						
						sheet.addCell(new Label(0,1,"科目名称",format2));
						sheet.addCell(new Label(1,1,"题目",format2));
						sheet.addCell(new Label(2,1,"A",format2));
						sheet.addCell(new Label(3,1,"B",format2));
						sheet.addCell(new Label(4,1,"C",format2));
						sheet.addCell(new Label(5,1,"D",format2));
						sheet.addCell(new Label(6,1,"参考答案",format2));
						sheet.addCell(new Label(7,1,"解析",format2));
						sheet.addCell(new Label(8,1,"分值",format2));
						
						int k=0;
						for(int j=row;j<stu.size();j++) {
							if(!cla.get(i).getCurName().equals(stu.get(j).getCurName())) {
								row=j;
								break;
							}
							sheet.addCell(new Label(0,k+2,stu.get(j).getCurName()+"",format3));
							sheet.addCell(new Label(1,k+2,stu.get(j).getTimuName(),format3));
							sheet.addCell(new Label(2,k+2,stu.get(j).getA()+"",format3));
							sheet.addCell(new Label(3,k+2,stu.get(j).getB()+"",format3));
							sheet.addCell(new Label(4,k+2,stu.get(j).getC()+"",format3));
							sheet.addCell(new Label(5,k+2,stu.get(j).getD()+"",format3));
							sheet.addCell(new Label(6,k+2,stu.get(j).getYes()+"",format3));
							sheet.addCell(new Label(7,k+2,stu.get(j).getJieXi()+"",format3));
							sheet.addCell(new Label(8,k+2,stu.get(j).getFenZhi()+"",format3));
							row=j;
							k++;
						}
					}
					//写
					book.write(); 
					//关 
					book.close();
					
					
					//下载
					try {
						su.downloadFile("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls");
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
				
			}else {
				try {
					
					//创建工作表 
					WritableSheet sheet=book.createSheet(classname+"",0); 
					
					
					//设置单元格的大小
					sheet.mergeCells(0, 0, 8, 0);
					sheet.setColumnView(0, 20);
					sheet.setColumnView(1, 30);
					sheet.setColumnView(2, 30);
					sheet.setColumnView(3, 30);
					sheet.setColumnView(4, 30);
					sheet.setColumnView(5, 30);
					sheet.setColumnView(6, 30);
					sheet.setColumnView(7, 30);
					sheet.setColumnView(8, 20);
					//设置单元格的字体
					WritableFont font1=new WritableFont(WritableFont.ARIAL,20,WritableFont.BOLD);
					WritableFont font2=new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
					WritableFont font3=new WritableFont(WritableFont.ARIAL,12,WritableFont.NO_BOLD);
					//设置单元格的格式
					WritableCellFormat format1=new WritableCellFormat(font1);
					format1.setAlignment(Alignment.CENTRE);
					//设置边框
					format1.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format2=new WritableCellFormat(font2);
					format2.setAlignment(Alignment.CENTRE);
					//设置边框
					format2.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					WritableCellFormat format3=new WritableCellFormat(font3);
					//设置边框
					format3.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
					
					
					//添加
					sheet.addCell(new Label(0,0,classname,format1));
					
					sheet.addCell(new Label(0,1,"科目名称",format2));
					sheet.addCell(new Label(1,1,"题目",format2));
					sheet.addCell(new Label(2,1,"A",format2));
					sheet.addCell(new Label(3,1,"B",format2));
					sheet.addCell(new Label(4,1,"C",format2));
					sheet.addCell(new Label(5,1,"D",format2));
					sheet.addCell(new Label(6,1,"参考答案",format2));
					sheet.addCell(new Label(7,1,"解析",format2));
					sheet.addCell(new Label(8,1,"分值",format2));
					
					
					ArrayList<QuestionInfo> list=qid.selectAll(classname);
					for(int i=0;i<list.size();i++) {
						sheet.addCell(new Label(0,i+2,list.get(i).getCurName()+"",format3));
						sheet.addCell(new Label(1,i+2,list.get(i).getTimuName()+"",format3));
						sheet.addCell(new Label(2,i+2,list.get(i).getA()+"",format3));
						sheet.addCell(new Label(3,i+2,list.get(i).getB()+"",format3));
						sheet.addCell(new Label(4,i+2,list.get(i).getC()+"",format3));
						sheet.addCell(new Label(5,i+2,list.get(i).getD()+"",format3));
						sheet.addCell(new Label(6,i+2,list.get(i).getYes()+"",format3));
						sheet.addCell(new Label(7,i+2,list.get(i).getJieXi()+"",format3));
						sheet.addCell(new Label(8,i+2,list.get(i).getFenZhi()+"",format3));
					}
					
					//写
					book.write(); 
			
					//关 
					book.close();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} 
					//下载
					try {
						su.downloadFile("C:/Program Files/Apache Software Foundation/Tomcat 9.0/wtpwebapps/ZCksxtSystem/Student/"+classname+".xls");
					} catch (Exception e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
			}
		
		}catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}


